
--1:
CREATE TABLE Author 
(
   Id  int  NOT NULL PRIMARY KEY identity,
   FirstName  nvarchar(45) NOT NULL,
   LastName  nvarchar(45) NOT NULL,
   UpdatedTime  datetime NOT NULL
 )
 --��FirstName����Ψһ����uniq_idx_firstname����LastName������ͨ����idx_lastname

--2:
-- ����һ��������trg_AuditLog������Employees���в���һ�����ݵ�ʱ�򣬴���������ص����ݵ�AuditLog�С�
 -- ְԱ��
CREATE TABLE Employees
(
   Id INT PRIMARY KEY NOT NULL identity,
   Name nvarchar(80) NOT NULL,
   Age INT NOT NULL,
   Address NVARCHAR(50),
   SALARY decimal(18,2)
);
-- �����־��
CREATE TABLE AuditLog
(
   Id int primary key not null identity,
   NAME TEXT NOT NULL,
   Salary decimal(18,2)
);

--��̨����Employees����һ������:
INSERT INTO Employees (NAME,AGE,ADDRESS,SALARY)VALUES ('Paul', 32, 'California', 20000.00 );
--Ȼ���AuditLog����ʹ�ò�ѯ���:
select * from AuditLog;
--�����
--Paul | 20000.00

select * from Employees


go
create trigger tr_trg_AuditLog
on Employees
instead of insert
as
begin
	declare @count int,@salary int 

	select @count=COUNT(*) from inserted

	if(@count>1)
		begin
			print  '����һ���͹���'
		end
	else
		begin
			select @salary=SALARY from inserted
			insert into AuditLog (NAME,Salary) select * from Employees where @salary=SALARY
		end
end
go

select * from AuditLog

--3:
--���Author��������ͼvw_Author��ֻ����FirstName�Լ�LastName���У���������������������FirstNameΪv_FirstName��LastName�޸�Ϊv_LastName��
go
CREATE TABLE Author 
(
   Id  int  NOT NULL PRIMARY KEY identity,
   FirstName  nvarchar(45) NOT NULL,
   LastName  nvarchar(45) NOT NULL,
   UpdatedTime  datetime NOT NULL
)
��̨�����2������:
insert into Author (FirstName,LastName,UpdatedTime) values ('PENELOPE', 'GUINESS', '2006-02-15 12:34:33'), ('NICK', 'WAHLBERG', '2006-02-15 12:34:33');

--��ѯ��ͼ��select * from vw_Author;
--�����
--['first_name_v', 'last_name_v']
--PENELOPE|GUINESS
--NICK|WAHLBERG

--4:
--��ҳ��ѯEmployees����ÿ5��һҳ�����ص�2ҳ������

CREATE TABLE Employees1111
(
	Y int ,
   Id int primary key  identity,
   EmployeeCode nvarchar(80),
   Birthday date ,
   FirstName nvarchar(14) ,
   LastName nvarchar(16) ,
   Gender char(1) ,
   HireDate date ,
   parentId int 
);
--����һЩ���ݣ�
	insert into Employees1111 (Y) values (1)
insert into Employees1111 (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate,parentId) values ('10001','1953-09-02','Georgi','Facello','M','1986-06-26',(select top 1 Y from Employees1111 where Y = 1))
INSERT INTO employees1111 (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate,parentId)  VALUES('10002','1964-06-02','Bezalel','Simmel','F','1985-11-21',(select top 1 Y from Employees1111 where Y = 1));
INSERT INTO employees1111 (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate,parentId)  VALUES('10003','1959-12-03','Parto','Bamford','M','1986-08-28',(select top 1 Y from Employees1111 where Y = 1));
INSERT INTO employees1111 (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate,parentId)  VALUES('10004','1954-05-01','Chirstian','Koblick','M','1986-12-01',(select top 1 Y from Employees1111 where Y = 1));
INSERT INTO employees1111 (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate,parentId)  VALUES('10005','1955-01-21','Kyoichi','Maliniak','M','1989-09-12',(select top 1 Y from Employees1111 where Y = 1));
	insert into Employees1111 (Y) values (2)
INSERT INTO employees1111 (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate,parentId)  VALUES('10006','1953-04-20','Anneke','Preusig','F','1989-06-02',(select top 1 Y from Employees1111 where Y = 2));
INSERT INTO employees1111 (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate,parentId)  VALUES('10007','1957-05-23','Tzvetan','Zielinski','F','1989-02-10',(select top 1 Y from Employees1111 where Y = 2));
INSERT INTO employees1111 (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate,parentId)  VALUES('10008','1958-02-19','Saniya','Kalloufi','M','1994-09-15',(select top 1 Y from Employees1111 where Y = 2));
INSERT INTO employees1111(EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate,parentId)  VALUES('10009','1952-04-19','Sumant','Peac','F','1985-02-18',(select top 1 Y from Employees1111 where Y = 2));
INSERT INTO employees1111(EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate,parentId)  VALUES('10010','1963-06-01','Duangkaew','Piveteau','F','1989-08-24',(select top 1 Y from Employees1111 where Y = 2));
	insert into Employees1111 (Y) values (3)
INSERT INTO employees1111 (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate,parentId)  VALUES('10011','1953-11-07','Mary','Sluis','F','1990-01-22',(select top 1 Y from Employees1111 where Y = 3))

select * from Employees1111

;with CTE_A
as
(
	select Id ,EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate,parentId,0 level from Employees1111 where id = 1
	union all
	select a.Id ,a.EmployeeCode,a.Birthday,a.FirstName,a.LastName,a.Gender,a.HireDate,a.parentId,1+level level from Employees1111 a , CTE_A b
	where a.Id =b.parentId

)select * from CTE_A



/*
5. ������30�֣�
�������յ�M��˾�Ŀ�����Լ���⿪��һ��OA�칫�Զ���ϵͳ����ϵͳ�Ĳ��ֹ��ܼ�������������Ľ������ ��

��1��M��˾������ҵ�񲿡��߻������������š�������Ϣ�������źš������������ܡ���ϵ�绰������ţ�ÿ������ֻ��һ�����ܣ�ֻ������������ŵĹ����������ܲ���Ա����ϵ��Ա���ţ�һ�������ж���Ա����ÿ��Ա�������ҽ�����һ�����š�

��2��Ա����Ϣ����Ա���š�������ְλ����ϵ��ʽ��н�ʡ�ְλ�������ܡ�ҵ��Ա�� �߻�Ա�ȡ�ҵ��Ա���������û����룬����������־��һ��ҵ��Ա������������û����룬��һ���û�����ֻ����һ��ҵ��Ա������

��3���û���Ϣ�����û��š��û����������˺š��绰����ϵ��ַ���û���Ψһ��ʶ�û���Ϣ�е�ÿһ��Ԫ�顣

��4���û�������Ϣ��������š��û��š��������ڡ��������λ��������ص㡢Ԥ���������־��������־�У������루Ĭ�ϣ����Ѿܾ����������������������ѹ��ڣ��������Ψһ��ʶ�û�������Ϣ�е�ÿһ��Ԫ�飬��һ���û������ύ������룬��һ���û�����ֻ��Ӧһ���û��š�

��5���߻�������Ϊ���������û������ƶ�����߻����񡣲߻������������š�������ϸ��Ҫ�����ʱ��.�����Ψһ��ʶ�߻������ÿһ��Ԫ�顣һ���߻�����ֻ��Ӧһ�����������û����룬��һ���߻�������ɶ����߻�Ա����ִ�У���һ���߻�Ա���Բ���ִ�У����޸Ĳ߻�����

�}Ŀ1��10�֣���ʹ��PowerDesigner����ƿ���ʹ�õ�SqlServer���ݿ�����ģ�ͣ�Ҫ���������ƺ�Ӣ�����������Ӣ�Ĳ�֪���Ŀ��Խ�������������������ϵ׼ȷ��������ƺõ�ģ������sql��䣬����һ����ΪOADatabase�����ݿ⣬���ã�
��Ŀ2��10�֣���Ϊ�˱�֤���û�������飬������־Ϊ �����룬�����һ�������������û�������Ϣ���������ݵ�ʱ�򣬿����ж���Ĭ��������־�Ƿ�Ϊ�������룬����ǣ������账�������������룬������������¸���������־Ϊ�������룻
��Ŀ3��10�֣�����ҵ��Ա�����û������ʱ����Ҫ����ǰ�����ļ�¼��������־�޸�Ϊ����������ͬʱ����������״̬Ϊ�������룬�һ��������ѳ����ļ�¼�е�������־����Ϊ���ѹ��ڣ������һ���洢������������ҵ��
*/
--2:
go
create trigger tr_a
on  Userapplyfors
instead of insert
as
begin
	declare @count int ,@acceptlog nvarchar(80) 
	select @count from inserted

	if (@count >1)
		begin
			print '�����룬���账��������������'
		end
	else
		begin
			select @acceptlog=acceptlog from inserted
			insert into Userapplyfors(acceptlog) values (@acceptlog)
			print '������'

		end
end
go

--3:
create proc proc_a
@res nvarchar(80)
as
begin
	declare @acceptlog nvarchar(80),@date date
	select @acceptlog=acceptlog,@date =date
		if (@acceptlog=1)
			begin
				set @res ='������'
			end
	else
		begin
			set @res ='������'
		end

		if (@date>date)
			begin
				set @res ='��ɾ��'
			end
end





